package cn.kgc.layui.dao.impl;

import cn.kgc.layui.dao.RoleDao;
import cn.kgc.layui.entity.Authority;
import cn.kgc.layui.entity.Role;
import cn.kgc.layui.entity.Student;
import cn.kgc.layui.util.DruidDBUtil;
import cn.kgc.layui.vo.RoleVO;
import cn.kgc.layui.vo.ZtreeVO;
import com.alibaba.druid.util.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class RoleDaoImpl implements RoleDao {
    @Override
    public List<Role> findAllByPage(int start, int size, Role role) {
        // 获取QueryRunner
        QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
        String sql="select role_id roleId,role_name roleName,comments,is_delete isDelete,role_code roleCode,create_time createTime,update_time updateTime  from sys_role where 1=1";


        try {

            String sqlWhere="";
            if(null!=role.getRoleName()&&(!StringUtils.isEmpty(role.getRoleName()))){
                sqlWhere+=" and role_name like'%"+role.getRoleName()+"%'";
            }
            if(null!=role.getRoleCode()&&(!StringUtils.isEmpty(role.getRoleCode()))){
                sqlWhere+=" and role_code like'%"+role.getRoleCode()+"%'";
            }
            if(null!=role.getComments()&&(!StringUtils.isEmpty(role.getComments()))){
                sqlWhere+=" and comments like'%"+role.getComments()+"%'";
            }
            sql=sql+sqlWhere;
            sql=sql+" limit ?,?";
            return qr.query(sql, new BeanListHandler<>(Role.class),start, size);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        return null;
    }

    /**
     * 根据用户id查询他拥有的角色列表
     * @return
     */
    public List<Role> getRolesByManageId(String managerId){
        // 获取QueryRunner

        try {
            QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
            String sql="select role_id roleId,role_name roleName from sys_role where role_id in( " +
                    "select role_id from sys_manager_role where manager_id=? " +
                    ")";
            return qr.query(sql, new BeanListHandler<>(Role.class),managerId);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;

    }

    /**
     * 查询角色数据封装layui所需要的前端数据结构
     * @return
     */
    public List<RoleVO> roleData() {
        // 获取QueryRunner
        QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
        String sql="select role_name name,role_id value from sys_role";


        try {

            return qr.query(sql, new BeanListHandler<>(RoleVO.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }


    @Override
    public int save(Role role) throws SQLException {
        int result=0;
        try {
            // 获取QueryRunner
            QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
            String sql="insert into sys_role (role_name,comments,is_delete,role_code,create_time) values(?,?,?,?,?)";
            result=qr.update(sql,role.getRoleName(),role.getComments(),role.getIsDelete(),role.getRoleCode(),role.getCreateTime());
            return result;
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 向角色权限关联表插入数据
     */
    public void saveRoleAuth(String roleId,String authIds) {
        String [] authIdArra= authIds.split(",");
        try {
            // 获取QueryRunner
            QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
            String sql="insert into sys_role_auth (role_id,auth_id) values(?,?)";
            qr.update("delete from sys_role_auth where role_id=?",roleId);
            for (String authId:authIdArra) {
                qr.update(sql,roleId,authId);
            }
        }catch (Exception e){
            e.printStackTrace();
        }


    }

    @Override
    public int update(Role role) throws SQLException {
        int result=0;
        try {
            // 获取QueryRunner
            QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
            String sql="update sys_role set role_name=?, comments=?,is_delete=?,role_code=?,update_time=? where role_id=?";
            result=qr.update(sql,role.getRoleName(),role.getComments(),role.getIsDelete(),role.getRoleCode(),role.getUpdateTime(),role.getRoleId());
            return result;
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public int delete(String id) throws SQLException {
        int result=0;
        try {
            // 获取QueryRunner
            QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
            String sql="delete from sys_role where role_id=?";
            result=qr.update(sql,id);
            return result;
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public long selectTotalCount() {
        QueryRunner qr = new QueryRunner(DruidDBUtil.getDatasource());
        String sql = "select count(*) from sys_role";
        try {
            return qr.query(sql,new ScalarHandler<Long>());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }
}
